package com.qp.basics.estivate;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

public abstract class BaseDatabase implements Database {
	private static final Log logger = LogFactory.getLog(BaseDatabase.class);
	protected ConnectionProvider connectionProvider;
	protected String databaseName;
	protected String tableSchema;
	protected String description;
	protected Table[] tables;
	protected String constrainSql;
	private String version;
	static {
//		databaseImpls.put("sqlserver",
//				"com.qp.estivate.sqlserver.DatabaseSqlserver");
		databaseImpls.put("mysql",
				"com.qp.basics.estivate.mysql.DatabaseMySql");
//		databaseImpls.put("oracle",
//				"com.qp.estivate.oracle.DatabaseOracle");
//		databaseImpls.put("sybase",
//				"com.qp.estivate.sybase.DatabaseSybase");
//		databaseImpls.put("derby",
//				"com.qp.estivate.derby.DatabaseDerby");
	}

	public static Database getDatabaseByType(String type) {

		String dbClassName = databaseImpls.get(type);

		Database db = null;

		try {
			db = (Database) (Class.forName(dbClassName)).newInstance();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return db;

	}

	protected void closeConn(Connection conn) {
		try {
			conn.close();
		} catch (SQLException e) {
			if (logger.isDebugEnabled())
				logger.debug(e.getMessage(), e);
		}
	}

	public void createTable(Table table) {
		String sql = this.getTableCreateSql(table);
		this.executeUpdate(sql);
		this.tables = null;
	}

	public Table getTable(String tableName) {
		if (tables == null) {
			tables = this.getTables();
		}
		if (tables != null) {
			for (int i = 0; i < tables.length; i++) {
				Table table = tables[i];
				if (table.getTableName().equalsIgnoreCase(tableName)) {
					return table;
				}
			}
		}
		return null;
	}

	// 检索由表的外键列（表导入的主键）引用的主键列的描述。 pk 是 parent 表
	//
	@Override
	public void addForeignKeys(Database db, Table fkTable) {
		fkTable.setRelationMap(new TreeMap<String, RelationShip>());
		ResultSet rs;
		Connection connection = null;
		try {
			connection = (Connection) db.getConnectionProvider()
					.getConnection();
			DatabaseMetaData metaData = connection.getMetaData();
			rs = metaData.getImportedKeys(null, this.tableSchema.toUpperCase(),
					fkTable.getTableName());
			while (rs.next()) {
				Table pkTable = this.getTable(rs.getString("PKTABLE_NAME"));
				if (pkTable != null) {
					fkTable.addForeignKeys(rs.getString("FK_NAME"), pkTable,
							pkTable.getColumn(rs.getString("PKCOLUMN_NAME")),
							null, null,
							fkTable.getColumn(rs.getString("FKCOLUMN_NAME")),
							rs.getInt("UPDATE_RULE"), rs.getInt("DELETE_RULE"),
							null, null);
				} else {
					System.out
							.println(" 关联的表没了！！！！！！！//可能是在外部关联就是remotetable 的概念了，现在先不弄这个了");
				}
			}

			Table pkTable = fkTable;
			rs = metaData.getExportedKeys(null, this.tableSchema.toUpperCase(),
					fkTable.getTableName());
			while (rs.next()) {
				Table fktable = this.getTable(rs.getString("FKTABLE_NAME"));
				if (fktable != null) {
					fkTable.addForeignKeys(rs.getString("FK_NAME"), null, null,
							fktable,
							fktable.getColumn(rs.getString("FKCOLUMN_NAME")),
							pkTable.getColumn(rs.getString("PKCOLUMN_NAME")),
							rs.getInt("UPDATE_RULE"), rs.getInt("DELETE_RULE"),
							null, null);
				} else {
					System.out
							.println(" 关联的表没了！！！！！！！//可能是在外部关联就是remotetable 的概念了，现在先不弄这个了");
				}
			}
			// getIndexInfo(String catalog, String schema, String table, boolean
			// unique, boolean approximate)
			// {table_cat=null, table_schem=NTTELCOM,
			// table_name=APP_USER, non_unique=0,
			// index_qualifier=null, index_name=SYS_C006026,
			// type=1, ordinal_position=1, column_name=F_USERNAME,
			// asc_or_desc=null, cardinality=1090, pages=3,
			// filter_condition=null}
			rs = metaData.getIndexInfo(null, this.tableSchema.toUpperCase(),
					fkTable.getTableName(), false, true);
			while (rs.next()) {
				String indexName = rs.getString("INDEX_NAME");
				String nonUnique = rs.getString("NON_UNIQUE");
				String columnName = rs.getString("COLUMN_NAME");
				if (indexName != null && !indexName.equals("")) {
					fkTable.addIndex(indexName, nonUnique,
							fkTable.getColumn(columnName));
				}
			}
			rs.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public List<Table> getNoPKTables() {

		Table[] tables = this.getTables();
		List<Table> noPKTables = new ArrayList<Table>();
		for (int i = 0; i < tables.length; i++) {
			Column[] pks = tables[i].getPks();
			if (pks.length == 0)
				noPKTables.add(tables[i]);
		}
		return noPKTables;
	}

	@Override
	public List<Column> getNoIndexForeignColumns() {
		Table[] tables = this.getTables();
		List<Column> noIndexForeignColumns = new ArrayList<Column>();

		for (int i = 0; i < tables.length; i++) {
			this.addForeignKeys(this, tables[i]);
			Map<String, RelationShip> relationMap = tables[i].getRelationMap();
			Map<String, Index> indexMap = tables[i].getIndexMap();

			for (RelationShip relationShip : relationMap.values()) {
				Column fkCol = relationShip.getChildColumn();
				if (!indexMap.containsValue(fkCol))
					noIndexForeignColumns.add(fkCol);
			}
		}
		return noIndexForeignColumns;
	}

	public void dropColumn(Table table, Column column) {
		// ALTER TABLE table_name DROP field_name;
		String tableName = table.getTableName();
		String newColName = column.getColumnName();
		String alterColumnSql = "ALTER TABLE " + tableName + " DROP "
				+ newColName + " ";

		executeUpdate(alterColumnSql);
		table.setColumns(this.getColumns(table));
	}

	public void dropTable(String tableName) {
		String sql = "drop table " + tableName;
		executeUpdate(sql);
		this.tables = null;
	}

	public void reset() {
		tables = null;

	}

	public ConnectionProvider getConnectionProvider() {
		return connectionProvider;
	}

	public String getDatabaseName() {
		return databaseName;
	}

	public void setConnectionProvider(ConnectionProvider connectionProvider) {
		this.connectionProvider = connectionProvider;
	}

	public void setDatabaseName(String databaseName) {
		this.databaseName = databaseName;
	}

	public String getTableSchema() {
		return tableSchema;
	}

	public void setTableSchema(String tableSchema) {
		this.tableSchema = tableSchema;
	}

	public String getDescription() {
		return description;
	}

	public void setDescription(String description) {
		this.description = description;
	}

	public void executeUpdate(String sql) {
		if (logger.isDebugEnabled())
			logger.debug(sql);
		Connection conn = null;
		try {
			conn = this.getConnectionProvider().getConnection();
			Statement sm = conn.createStatement();
			// 这种简单的分割还是太危险了，并且也不应该放在这里。
			// String[] sqlArr = sql.split(";") ;
			// for (String str : sqlArr) {
			// if(!str.toString().trim().equals(""))
			// sm.addBatch(str) ;
			// }
			sm.addBatch(sql);
			sm.executeBatch();
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage() + " sql=" + sql, e);
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
	}

	protected Object getValue(ResultSet rs, int colIndex) throws SQLException {
		return rs.getObject(colIndex);
	}

	@SuppressWarnings("unchecked")
	protected Map resultSetToMap(ResultSet rs) {
		if (rs == null) {
			return null;
		}
		Map rsMap = new IgnoreCaseMap();
		int columnCount;
		try {
			java.sql.ResultSetMetaData metaData = rs.getMetaData();
			columnCount = metaData.getColumnCount();
			for (int i = 0; i < columnCount; i++) {
				String colName = metaData.getColumnLabel(i + 1);
				rsMap.put(colName, getValue(rs, i + 1));
			}
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage(), e);
		}
		return rsMap;
	}

	@SuppressWarnings("unchecked")
	protected List resultSetToList(ResultSet rs) {
		if (rs == null)
			return null;
		List rsList = new ArrayList();
		try {
			while (rs.next()) {
				Map m = this.resultSetToMap(rs);
				rsList.add(m);
			}

		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage(), e);
		}
		return rsList;
	}

	@Override
	public void exportDatabase(String exportType, String exportTo) {
		throw new RuntimeException("尚未实现。");
	}

	@Override
	public void importDatabase(String importType, String importFrom) {
		throw new RuntimeException("尚未实现。");
	}

	/**
	 * select u.f_id, (case when u.f_username <> u2.f_username or (u.f_username
	 * is null and u2.f_username is not null) or (u.f_username is not null and
	 * u2.f_username is null) then '用户名由[' || u.f_username || ']改为了[' ||
	 * u2.f_username || ']' end) || (case when u.f_address <> u2.f_address or
	 * (u.f_address is null and u2.f_address is not null) or (u.f_address is not
	 * null and u2.f_address is null) then ';地址由[' || u.f_address || ']改为了[' ||
	 * u2.f_address || ']' end)
	 * 
	 * as 用户名
	 * 
	 * from app_user u join js2.app_user u2 on u.f_id = u2.f_id where
	 * u.f_address <> u2.f_address or u.f_username <> u2.f_username
	 * 
	 * @param tableNames
	 *            检查的表
	 * @param tableSchema
	 *            对比的原始数据
	 * @param tableSchemaO
	 *            对比的比较数据
	 */
	public List<List> dataCheck(String[] tableNames, String tableSchema,
			String tableSchemaO) {
		List<List> sqlStrList = new ArrayList<List>();
		// 各种数据库的字符串连接
		for (int i = 0; i < tableNames.length; i++) {
			String tableName = tableNames[i];
			Table table = getTable(tableName);
			Column[] pkColumns = table.getPks();
			Column[] columns = table.getColumns();
			List<Column> nonPkColumns = new ArrayList<Column>();
			for (int j = 0; j < columns.length; j++) {
				if (columns[j].isPk)
					continue;
				nonPkColumns.add(columns[j]);
			}

			String columnChangeSql = "";
			String selectSql = " select ";
			String whereSql = "  where ";
			String fromSql = "  from  " + tableName + "  T1 join "
					+ tableSchemaO + "." + tableName + "  T2  on  ";

			for (int j = 0; j < pkColumns.length; j++) {
				if (j == 0) {
					fromSql += "  T1." + pkColumns[j].columnName + " = T2."
							+ pkColumns[j].columnName;
				} else {
					fromSql += "  and  T1." + pkColumns[j].columnName
							+ " = T2." + pkColumns[j].columnName;
				}
				selectSql += "T1." + pkColumns[j].columnName + " , ";
			}

			for (int j = 0; j < nonPkColumns.size(); j++) {
				Column column = nonPkColumns.get(j);
				String columnName = column.getColumnName();
				String comment = column.getComment();
				if (comment == null || comment.equals("")) {
					comment = columnName;
				}
				if (j != 0) {
					columnChangeSql += " ||   ";
				}
				columnChangeSql += " (case   when " + " T1." + columnName
						+ " <> T2." + columnName + " or " + " ( T1."
						+ columnName + " is null and T2." + columnName
						+ " is not null) or  " + " (  T1." + columnName
						+ "  is not null and  T2." + columnName + " is null)"
						+ "  then  " + "  '" + comment + "[' || T1."
						+ columnName + " || ']改为了[' || T2." + columnName
						+ " || ']'  " + " end )   ";

				// whereSql
				if (j != 0) {
					whereSql += " or ";
				}
				whereSql += "  T1." + columnName + " <> T2." + columnName;
			}
			List<String> sqlList = new ArrayList<String>();
			String sqlStr = selectSql + columnChangeSql + fromSql + whereSql;

			String addSql = " select * from " + tableName
					+ "  T1 	where T1.F_ID not in (" + "	select T2.F_ID  from "
					+ tableSchemaO + "." + tableName + " T2 )";
			String deleteSql = " select *  from " + tableSchemaO + "."
					+ tableName + " T2  where T2.F_ID not in ("
					+ "	select T1.F_ID from " + tableName + "  T1  )";
			sqlList.add(sqlStr);
			sqlList.add(addSql);
			sqlList.add(deleteSql);

			sqlStrList.add(sqlList);
		}
		// SELECT concat(`help_category_id`, `name`) FROM `help_category` WHERE
		// 1

		return sqlStrList;
	}

	public String getCurrentDataBaseType() {
		return this.getClass().getName();
	}

	public String getVersion() {
		return version;
	}

	public void setVersion(String version) {
		this.version = version;
	}
	
	
	public List<Database> getAvailableDatabases(){
		return null;
	}
}
